var models = require('../db');
var express = require('express');
// const jwt = require('jsonwebtoken');
// var uuid = require('node-uuid');
var router = express.Router();
var mysql = require('mysql');
var $sql = require('../sql');
const req = require('express/lib/request');

// 连接数据库
var conn = mysql.createConnection(models.mysql);
conn.connect();

// 查询用户列表
router.post('/getStudentList', (req, res) => {
  var pageNum = req.body.pageNum // 当前的num
  var pageSize = req.body.pageSize //当前页的数量
  var sql = $sql.studentInfo.queryStudentList;
  let content = [];
  let isMore = false;// 是否有多个查询参数
  // 姓名
  if(req.body.queryList.studentName !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    sql += "studentName LIKE ?";
    content.push( "%"+req.body.queryList.studentName+"%" );
    isMore = true;
  }
  // 学号
  if(req.body.queryList.studentId !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    sql += "studentId LIKE ?";
    content.push( "%"+req.body.queryList.studentId+"%" );
    isMore = true;
  }
  // 学院
  if(req.body.queryList.department !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    sql += "department LIKE ?";
    content.push( "%"+req.body.queryList.department+"%" );
    isMore = true;
  }
  // 年级
  if(req.body.queryList.grade !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    sql += "grade LIKE ?";
    content.push( "%"+req.body.queryList.grade+"%" );
    isMore = true;
  }
  // 宿舍楼
  if(req.body.queryList.buildNo !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    sql += "buildNo LIKE ?";
    content.push( "%"+req.body.queryList.buildNo+"%" );
    isMore = true;
  }
  // 宿舍号
  if(req.body.queryList.dormNo !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    sql += "dormNo LIKE ?";
    content.push( "%"+req.body.queryList.dormNo+"%" );
    isMore = true;
  }
  // 辅导员
  if(req.body.queryList.counselor !== ''){
    if (isMore){
      sql += " and "
    }else {
      sql +=" where "
    }
    sql += "counselor LIKE ?";
    content.push( "%"+req.body.queryList.counselor+"%" );
    isMore = true;
  }
  // 未退宿
  if (isMore){
    sql += " and isNull != '2'"
  }else {
    sql +=" where isNull != '2'"
  }
  // 分页
  sql += " limit ? , ?";
  content.push((parseInt(pageNum) - 1) * parseInt(pageSize));
  content.push(parseInt(pageSize));
  isMore = false;
  
  conn.query(sql, content, function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    if (err) {
      return res.send({
        code: 1,
        message: '查询失败'
      })
    }else{
      var sqlTotal = 'select count(*) as total from student_info' //as更换名称
      conn.query(sqlTotal, function (error, result) {
        if (error) {
        } else {
          var total = result[0]['total'] //查询表中的数量
          return res.send({
            result: 1,
            status: 200,
            message: "success",
            list: data,
            paging:{
              pageNum:pageNum,
              pageSize:pageSize,
              total:total
            }
          })
        }
      })
    }
  })
});

//查询所有宿舍楼号
router.post('/getBuildNo', (req, res) => {
  var sql = $sql.studentInfo.queryBuildNo;
  conn.query(sql, [], function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 1,
      msg: "查询成功",
      list: data
    })
  })
});
// 查询所有宿舍号
router.post('/addDormNo', (req, res) => {
  var sql = $sql.studentInfo.addDormNo;
  conn.query(sql, [req.body.buildNo], function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 1,
      msg: "查询成功",
      list: data
    })
  })
});
// 查询所有床位
router.post('/addBedNo', (req, res) => {
  var sql = $sql.studentInfo.addBedNo;
  conn.query(sql, [req.body.buildNo,req.body.dormNo,req.body.isLive], function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 1,
      msg: "查询成功",
      list: data
    })
  })
});
// 重置密码
router.post('/resetPassword', (req, res) => {
  var sql = $sql.studentInfo.resetPassword;
  conn.query(sql, [req.body.studentId], function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 1,
      msg: "重置成功",
      list: data
    })
  })
});
// 修改住宿流程
router.post('/editStudentDorm',(req,res) => {
  //将原数据备份到表change_record
  var sql = $sql.studentInfo.record;
  conn.query(sql, [req.body.studentId], function (err, result) {});
  console.log(sql);
  console.log(req.body.studentId);
  // 
  sql = $sql.studentInfo.nolive;
  conn.query(sql, [req.body.studentId,req.body.studentId,req.body.studentId], function (err, result) {})
  sql = $sql.studentInfo.update;
  conn.query(sql, [1,req.body.buildNo,req.body.dormNo,req.body.bedNo,req.body.buildNo,req.body.dormNo,req.body.bedNo,req.body.studentId], function (err, result) {})
  sql = $sql.studentInfo.havelive;
  conn.query(sql, [req.body.studentId,req.body.studentId,req.body.studentId], function (err, result) {
    return res.send({
      status: 1,
      msg: "信息已修改成功",
    })
  });
}),
// 删除住宿信息
router.post('/deleteStudentDorm',(req,res) => {
  var sql = $sql.studentInfo.record;
  conn.query(sql, [req.body.studentId], function (err, result) {});
  sql = $sql.studentInfo.nolive;
  conn.query(sql, [req.body.studentId,req.body.studentId,req.body.studentId], function (err, result) {})
  sql = $sql.studentInfo.update;
  conn.query(sql, [0,req.body.buildNo,req.body.dormNo,req.body.bedNo,req.body.buildNo,req.body.dormNo,req.body.bedNo,req.body.studentId], function (err, result) {
    return res.send({
      status: 1,
      msg: "住宿信息删除成功",
    })
  });
}),
// 新增用户
router.post('/addStudentDorm',(req,res) => {
  var sql = $sql.studentInfo.addStudentDorm;
  conn.query(sql, [req.body.commit.studentId,req.body.commit.studentName,req.body.commit.sex,req.body.commit.idCard,req.body.commit.department,req.body.commit.professional,req.body.commit.buildNo,req.body.commit.dormNo,req.body.commit.bedNo,req.body.commit.dormType,req.body.commit.address,req.body.commit.counselor,req.body.commit.coPhone], function (err, result) {
    return res.send({
      status: 200,
      msg: "新增用户信息成功",
    })
  });
  sql = $sql.studentInfo.havelive;
  conn.query(sql, [req.body.commit.studentId,req.body.commit.studentId,req.body.commit.studentId], function (err, result) {})
}),
router.post('/newsList', (req, res) => {
  var sql = $sql.studentInfo.newsList;
  conn.query(sql, [], function (err, result) {
    var data = JSON.parse(JSON.stringify(result))
    return res.send({
      status: 200,
      msg: "查询成功",
      list: data
    })
  })
});
router.post('/addnewsList', (req, res) => {
  var sql = $sql.studentInfo.addnewsList;
  conn.query(sql, [req.body.title,req.body.date,req.body.link], function (err, result) {
    return res.send({
      status: 200,
      msg: "新增成功",
    })
  })
});
module.exports = router;
